package work;

import org.apache.poi.ss.usermodel.*;
import work.config.Constant;
import work.fram.JDBCConnect;
import work.fram.WriterMaker;

import java.io.File;
import java.io.FileInputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class T {


    public static void main(String[] args) throws SQLException {

        Statement statement = JDBCConnect.getStatement();

        List<Map<String, String>> list = e2m();

        WriterMaker.MyWriter hasExist = WriterMaker.create("已经存在的记录.txt");
        WriterMaker.MyWriter hasMany = WriterMaker.create("存在多个父节点.txt");
        String createTime = "2019-11-25 00:01:02";
        //进行标记

        //获取一个连接


        Map<String, Map<String, String>> pnMap = new HashMap<>();

        for (Map<String, String> map : list) {

            //首先放入组织机构名称,id
            String name = map.get("组织名称");
            pnMap.put(name, map);

            //首先判断一下数据库中是否已经存在了该组织机构

            String isExistSql = "SELECT COUNT(id) FROM bus_org WHERE name=\"" + name + "\"";

            ResultSet resultSet = statement.executeQuery(isExistSql);

            //更新一个连接
            statement = JDBCConnect.getStatement();


            while (resultSet.next()) {

                int anInt = resultSet.getInt(1);
                if (anInt == 1) {

                    //记录一次
                    hasExist.writeLine(map.toString());

                } else {

                    //生成一个UUID
                    String id = UUID.randomUUID().toString();

                    String sql = "insert bus_org (name,short_name,create_time,id) values(\"" + name + "\",\"" + name + "\",\"" + createTime + "\",\"" + id + "\")";

                    statement.execute(sql);

                }

            }


        }


        //查询出所有未设置名称的节点

        ResultSet resultSet = statement.executeQuery("select id,name from bus_org where create_time=\"" + createTime + "\" and code is null");
        statement = JDBCConnect.getStatement();

        List<Map<String, String>> all = new ArrayList<>();
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            String id = resultSet.getString("id");
            Map<String, String> map = new HashMap<>();
            map.put("name", name);
            map.put("id", id);
            all.add(map);
        }

        //进入一个循环
        while (true) {


            List<Map<String, String>> newAll = new ArrayList<>();

            //遍历all
            for (Map<String, String> map : all) {

                //根据名称从数据库中获取父节点的id和code
                ResultSet query = statement.executeQuery("select id,code from bus_org where name=\"" + pnMap.get(map.get("name")).get("上级党组织名称") + "\"");

                //跳转到最后一行
                query.last();

                if (query.getRow() > 1) {
                    hasMany.writeLine("找到多于1个父节点:" + map);
                } else if (query.getRow() == 0) {
                    hasMany.writeLine("没有找到父节点:" + map);
                } else {

                    String code = query.getString("code");
                    if (code == null) {
                        newAll.add(map);//重新添加,交给下一轮进行处理
                    } else {

                        String c = code + "-" + pnMap.get(map.get("name")).get("党组织编码");

                        //设置code
                        String sql = "update bus_org set code=\"" + c + "\", pid=\"" + query.getString("id") + "\" where id=\"" + map.get("id") + "\"";

                        //  System.out.println(sql);

                        statement.execute(sql);

                    }

                }

            }


            //如果上下两轮操作并没有产生任何改变就退出
            if (newAll.size() == all.size()) {
                break;
            }

            all = newAll;
        }

        Set<String> names = new HashSet<>();

        for (Map<String, String> map : all) {

            //进行一个深度的搜索,判断哪里为空

            names.add(findNull(pnMap, map.get("name")));

        }

        System.out.println(names);

    }

    private static String findNull(Map<String, Map<String, String>> pnMap, String name) {

        String n = name;

        while (true){

            Map<String, String> map = pnMap.get(n);

            //表明一个到极限了
            if (map == null) {
                break;
            } else {
                //递归
                n = map.get("上级党组织名称");
            }

        }

        return n;

    }


    //将Excel表格读取成文一个List的Map
    private static List<Map<String, String>> e2m() {

        List<Map<String, String>> list = new ArrayList<>();
        try {

            File ef = new File(Constant.work, "2019年党组织信息 - 副本.xls");

            Workbook sheets = WorkbookFactory.create(new FileInputStream(ef));

            Sheet rows = sheets.getSheet("党组织信息");


            //首先找到第二行
            Row index = rows.getRow(1);//这一行为所银行

            short lastCellNum = index.getLastCellNum();
            String names[] = new String[lastCellNum];

            for (int i = 0; i < lastCellNum; i++) {
                names[i] = index.getCell(i).getStringCellValue();
            }

            //递归直到最后一行
            for (int i = 2; i < rows.getLastRowNum() + 1; i++) {//

                //找到行
                Row row = rows.getRow(i);

                Map<String, String> map = new HashMap<>();

                for (int j = 0; j < names.length; j++) {

                    String name = names[j];
                    Cell cell = row.getCell(j);
                    map.put(name, cell.getStringCellValue());
                }

                //添加进list
                list.add(map);

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;

    }


}

